﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;

    public class PrintReportDA : IPrintReportDA
    {
        public List<EncasingDetailNote> GetEncasingDetailList(string OrderID, PrintReportsInfo tempPrintReportsInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("     SELECT OD.LINE_ID,SC.SKU_ID,S.DESCR,SC.QTY,OD.UOM_ID,SC.CONTAINER_ID,SC.CONTAINER_TYPE,OD.REMARK  FROM    ");
            strSql.AppendFormat(" {0}SHIPMENT_CONTAINER SC  LEFT JOIN {0}ORDER_DETAIL  OD  ON SC.ORDER_ID = OD.ORDER_ID  AND SC.ORDER_LINE_ID=OD.LINE_ID  LEFT JOIN {0}SKU S ON SC.SKU_ID=S.SKU_ID AND SC.OWNER_ID = S.OWNER_ID ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat("  WHERE  SC.ORDER_ID='{0}' AND SC.IS_ACTIVE='Y'", OrderID);
            List<EncasingDetailNote> encasingDetailNoteList = new List<EncasingDetailNote>();
            EncasingDetailNote encasingDetailNoteInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    encasingDetailNoteInfo = new EncasingDetailNote(reader["LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["DESCR"].ToString(), Convert.ToDecimal(reader["QTY"]), reader["UOM_ID"].ToString(), reader["CONTAINER_ID"].ToString(), reader["CONTAINER_TYPE"].ToString(), reader["REMARK"].ToString());
                    encasingDetailNoteList.Add(encasingDetailNoteInfo);
                }
            }
            return encasingDetailNoteList;
        }

        public DataSet GetEncasingDetailTitle(string orderID, string containerID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintEncasingDetail");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintEncasingDetail");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID).Replace("@ORDER_ID", "'" + orderID + "'").Replace("@CONTAINER_ID", containerID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetEncasingNote(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintEncasingNote");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintEncasingNote");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public List<PrintPickDetailRptInfo> GetPickDetailRptInfoListByOrderID(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT PD.ROWID,PD.WH_ID,PD.PICK_DETAIL_ID,PD.CASE_ID,PD.ORDER_ID,PD.ORDER_LINE_ID,PD.PREALLOCATE_PICK_DETAIL_ID,PD.LOT,PD.OWNER_ID,PD.SKU_ID,PD.ALT_SKU_ID,PD.UOM_ID,PD.QTY,PD.UOM_QTY,PD.MOVED_QTY,PD.STATUS,PD.DROP_ID,PD.LOC,PD.LPN_ID,PD.PACK_ID,PD.UPDATE_SOURCE,PD.CARTON_GROUP,PD.CARTON_TYPE,PD.TO_LOC,PD.DO_REPLENISH,PD.REPLENISH_ZONE,PD.DO_CARTONIZE,PD.PICK_METHOD,PD.WAVE_ID,PD.EFFECTIVE_DATE,PD.FROM_LOC,PD.TRACKING_ID,PD.FREIGHT_CHARGES,PD.INTER_MODAL_VEHICLE,PD.LOAD_ID,PD.STOP,PD.DOOR,PD.ROUTE,PD.SORTATION_LOCATION,PD.SORTATION_STATION,PD.BATCH_CARTON_ID,PD.IS_CLOSED,PD.QC_STATUS,PD.PD_UDF1,PD.PD_UDF2,PD.PD_UDF3,PD.PICK_NOTES,PD.RECEIPT_ID,PD.CROSS_DOCKED,PD.SEQ_NO,PD.LABEL_TYPE,PD.COMPANY_PREFIX,PD.SERIAL_REFERENCE,PD.OPTIMIZE_COP,PD.QTY_REJECTED,PD.REJECTED_REASON,PD.TS,PD.CREATED_BY,PD.CREATED_DATE,PD.UPDATED_BY,PD.UPDATED_DATE,SKU.DESCR AS SKU_DESCR,SKU.DESCR2 AS SKU_DESCR2,SKU.CUBE*PD.QTY AS TOTAL_CUBE,SKU.GROSS_WGT*PD.QTY AS TOTAL_WGT ,UOM.UOM_DESCR,LA.LOT_ATTR01,  LA.LOT_ATTR02, LA.LOT_ATTR03, LA.LOT_ATTR04, LA.LOT_ATTR05, LA.LOT_ATTR06, LA.LOT_ATTR07, LA.LOT_ATTR08, LA.LOT_ATTR09, LA.LOT_ATTR10,  LA.PRODUCE_DATE, LA.EXPIRY_DATE, LA.RECEIPT_DATE, LA.EXTERNAL_LOT, LA.VENDOR_ID,OD.ORIGINAL_QTY,OD.EXTERN_ORDER_ID,OD.EXTERN_LINE_ID,OD.REMARK");
            strSql.AppendFormat(" FROM  {0}PICK_DETAIL PD LEFT JOIN  {0}SKU SKU ON SKU.OWNER_ID = PD.OWNER_ID AND SKU.SKU_ID = PD.SKU_ID  LEFT JOIN {0}UOM UOM ON PD.UOM_ID = UOM.UOM_ID  LEFT JOIN {0}LOT_ATTRIBUTE LA ON PD.LOT=LA.LOT LEFT JOIN {0}ORDER_DETAIL OD ON PD.ORDER_ID=OD.ORDER_ID AND PD.ORDER_LINE_ID=OD.LINE_ID ", tempPrintReportsInfo.WhLoignID);
            strSql.Append("  WHERE PD.ORDER_ID = @ORDER_ID ");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            List<PrintPickDetailRptInfo> printPickDetailRptListInfo = new List<PrintPickDetailRptInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.ToString(), paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    PrintPickDetailRptInfo pickDetailInfo = new PrintPickDetailRptInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), reader["CASE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PREALLOCATE_PICK_DETAIL_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["MOVED_QTY"].ToString() != "") ? decimal.Parse(reader["MOVED_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["DROP_ID"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["CARTON_TYPE"].ToString(), reader["TO_LOC"].ToString(), reader["DO_REPLENISH"].ToString(), reader["REPLENISH_ZONE"].ToString(), reader["DO_CARTONIZE"].ToString(), reader["PICK_METHOD"].ToString(), reader["WAVE_ID"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["FROM_LOC"].ToString(), reader["TRACKING_ID"].ToString(), (reader["FREIGHT_CHARGES"].ToString() != "") ? ((double) float.Parse(reader["FREIGHT_CHARGES"].ToString())) : ((double) 0f), reader["INTER_MODAL_VEHICLE"].ToString(), reader["LOAD_ID"].ToString(), (reader["STOP"].ToString() != "") ? int.Parse(reader["STOP"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["SORTATION_STATION"].ToString(), reader["BATCH_CARTON_ID"].ToString(), reader["IS_CLOSED"].ToString(), reader["QC_STATUS"].ToString(), reader["PD_UDF1"].ToString(), reader["PD_UDF2"].ToString(), reader["PD_UDF3"].ToString(), reader["PICK_NOTES"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CROSS_DOCKED"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["LABEL_TYPE"].ToString(), reader["COMPANY_PREFIX"].ToString(), reader["SERIAL_REFERENCE"].ToString(), reader["OPTIMIZE_COP"].ToString(), (reader["QTY_REJECTED"].ToString() != "") ? decimal.Parse(reader["QTY_REJECTED"].ToString()) : 0M, reader["REJECTED_REASON"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERN_ORDER_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["UOM_DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString(), reader["SKU_DESCR2"].ToString(), (reader["TOTAL_CUBE"].ToString() != "") ? float.Parse(reader["TOTAL_CUBE"].ToString()) : 0f, (reader["TOTAL_WGT"].ToString() != "") ? float.Parse(reader["TOTAL_WGT"].ToString()) : 0f, (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, reader["EXTERN_LINE_ID"].ToString(), reader["REMARK"].ToString());
                    printPickDetailRptListInfo.Add(pickDetailInfo);
                }
            }
            return printPickDetailRptListInfo;
        }

        public DataSet GetPrintAllocation(string receiptID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = string.Empty;
            sql = ("SELECT IS_PALLETIZED FROM @_@RECEIPT WHERE RECEIPT_ID = '" + receiptID + "'").Replace("@_@", tempPrintReportsInfo.WhLoignID);
            if (DBHelper.ExecuteScalar(CommandType.Text, sql).ToString().ToUpper() == "N")
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintAllocation");
                if (string.IsNullOrEmpty(sql))
                {
                    sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintAllocation");
                }
            }
            else
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintAllocation_Palletized");
                if (string.IsNullOrEmpty(sql))
                {
                    sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintAllocation_Palletized");
                }
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintBatchOutboundListReport(string waveID, string orderIDs, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintBatchOutboundList");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintBatchOutboundList");
            }
            sql = string.Format(sql, orderIDs).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetPrintBatchPickAdviceReport(string waveID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintBatchPickAdvice");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintBatchPickAdvice");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintBatchPickAdviceReportByOrders(string orderIDs, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintBatchPickAdviceByOrders");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintBatchPickAdviceByOrders");
            }
            string sqlOrders = "'" + orderIDs.Replace(",", "','") + "'";
            sql = string.Format(sql, sqlOrders).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public List<PrintPickListDetailReportInfo> GetPrintBatchPickListDetailReportInfo(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT PD.SKU_ID,PD.ALT_SKU_ID,SKU.DESCR,SKU.DESCR2,SKU.UDF2 AS SKU_UDF2,PD.LOC AS FROM_LOC,PD.TO_LOC,UOM.UOM_DESCR,SUM(PD.QTY) AS QTY, SUM(PD.QTY) * SKU.GROSS_WGT AS GROSS_WGT, SUM(PD.QTY) * SKU.[CUBE] AS [CUBE],");
            strSql.Append(" PD.LPN_ID, '' AS FINAL_LOC, '' AS REMARK,PD.STATUS, SKU.PACK_ID,UOM.UOM_ID,PACK.PACK_UOM3, PACK.CASE_CNT,");
            strSql.Append(" LA.LOT_ATTR09,LA.EXTERNAL_LOT,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.VENDOR_ID,LAC.DESCR AS SKU_PROPERTY");
            strSql.AppendFormat(" FROM {0}PICK_DETAIL PD ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" LEFT JOIN {0}SKU SKU ON PD.SKU_ID = SKU.SKU_ID AND PD.OWNER_ID = SKU.OWNER_ID ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" LEFT JOIN {0}UOM UOM ON PD.UOM_ID = UOM.UOM_ID  ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" LEFT JOIN {0}PACK PACK ON PD.PACK_ID = PACK.PACK_ID ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" LEFT JOIN {0}LOT_ATTRIBUTE LA ON LA.LOT = PD.LOT", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" LEFT JOIN {0}LOT_ATTRIBUTE_CODE AS LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" WHERE PD.ORDER_ID ='{0}' AND  (PD.STATUS ='NEW' OR PD.STATUS ='RELEASED') ", orderID);
            strSql.Append(" GROUP BY PD.SKU_ID,PD.ALT_SKU_ID,SKU.DESCR,SKU.DESCR2,SKU.UDF2,PD.LOC,PD.TO_LOC,UOM.UOM_DESCR,PD.LPN_ID,PD.STATUS, SKU.PACK_ID,UOM.UOM_ID,PACK.PACK_UOM3,PACK.CASE_CNT,");
            strSql.Append(" LA.LOT_ATTR09,LA.EXTERNAL_LOT,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.VENDOR_ID,LAC.DESCR,SKU.GROSS_WGT,SKU.[CUBE]");
            List<PrintPickListDetailReportInfo> printPickListDetailReportInfoList = new List<PrintPickListDetailReportInfo>();
            PrintPickListDetailReportInfo printPickListDetailReportInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    DateTime CS00001;
                    printPickListDetailReportInfo = new PrintPickListDetailReportInfo(reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["DESCR2"].ToString(), reader["FROM_LOC"].ToString(), reader["TO_LOC"].ToString(), reader["UOM_DESCR"].ToString(), decimal.Parse(reader["QTY"].ToString()), reader["LPN_ID"].ToString(), reader["FINAL_LOC"].ToString(), reader["REMARK"].ToString(), reader["STATUS"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_UOM3"].ToString(), decimal.Parse(reader["CASE_CNT"].ToString()), "", "", "", "", "", "", "", "", reader["LOT_ATTR09"].ToString(), "", reader["EXTERNAL_LOT"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00001 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00001 = new DateTime()), reader["VENDOR_ID"].ToString(), reader["SKU_PROPERTY"].ToString(), reader["SKU_UDF2"].ToString(), decimal.Parse(reader["GROSS_WGT"].ToString()), decimal.Parse(reader["CUBE"].ToString()));
                    printPickListDetailReportInfoList.Add(printPickListDetailReportInfo);
                }
            }
            return printPickListDetailReportInfoList;
        }

        public DataSet GetPrintBatchPickListReportInfo(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintBatchPickList");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintBatchPickList");
            }
            sql = string.Format(sql, orderID).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetPrintBatchShippingOrderReport(string waveID, string orderIDs, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintBatchShippingOrder");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintBatchShippingOrder");
            }
            sql = string.Format(sql, orderIDs).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetPrintCarNoticeSet(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = string.Empty;
            sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintCarNoticeSet");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintCarNoticeSet");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", tempPrintReportsInfo.UserLanguage) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public List<PrintCommoditySignoffDetailInfo> GetPrintCommoditySignoffDetailInfo(string OrderID, PrintReportsInfo tempPrintReportsInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT PD.ORDER_ID, PD.SKU_ID, PD.ALT_SKU_ID, SKU.DESCR AS SKU_DESCR, SKU.DESCR2 AS SKU_DESCR2, PD.UOM_ID, UOM.UOM_DESCR, PD.PACK_ID, SUM(OD.ORIGINAL_QTY) AS ORIGINAL_QTY, SUM(OD.ORIGINAL_QTY) / (CASE PACK.CASE_CNT WHEN 0 THEN 1 ELSE PACK.CASE_CNT END) AS CASE_ORIGINAL_QTY, SUM(PD.QTY) AS QTY, SUM(PD.QTY) * SKU.GROSS_WGT AS GROSS_WGT, SUM(PD.QTY) * SKU.[CUBE] AS CUBE, PACK.PACK_UOM3 AS PACK_UOM, UOM_PACK.UOM_DESCR AS PACK_UOM_DESCR, PACK.CASE_CNT, SUM(PD.QTY) / (CASE PACK.CASE_CNT WHEN 0 THEN 1 ELSE PACK.CASE_CNT END) AS CASE_QTY, LA.EXTERNAL_LOT, OD.REMARK, VENDOR.VENDOR_ID, VENDOR.COMPANY AS VENDOR_NAME, LA.PRODUCE_DATE, LA.LOT_ATTR09, LAC.DESCR AS SKU_PROPERTY");
            strSql.Append(" FROM @_@PICK_DETAIL PD LEFT JOIN @_@ORDER_DETAIL OD ON OD.ORDER_ID = PD.ORDER_ID AND OD.LINE_ID = PD.ORDER_LINE_ID");
            strSql.Append(" LEFT JOIN @_@SKU SKU ON SKU.SKU_ID = PD.SKU_ID AND SKU.OWNER_ID = PD.OWNER_ID");
            strSql.Append(" LEFT JOIN @_@UOM UOM ON UOM.UOM_ID = PD.UOM_ID");
            strSql.Append(" LEFT JOIN @_@VENDOR VENDOR ON OD.VENDOR_ID = VENDOR.VENDOR_ID");
            strSql.Append(" LEFT JOIN @_@PACK PACK ON PACK.PACK_ID = PD.PACK_ID");
            strSql.Append(" LEFT JOIN @_@UOM UOM_PACK ON UOM_PACK.UOM_ID = PACK.PACK_UOM3");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.LOT = PD.LOT");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE AS LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'");
            strSql.Append(" WHERE PD.[STATUS] = 'Shipped' AND PD.ORDER_ID = @ORDER_ID");
            strSql.Append(" GROUP BY PD.ORDER_ID, PD.SKU_ID, PD.ALT_SKU_ID, SKU.DESCR, SKU.DESCR2, PD.UOM_ID, UOM.UOM_DESCR, PD.PACK_ID, SKU.GROSS_WGT, SKU.CUBE, PACK.PACK_UOM3, UOM_PACK.UOM_DESCR, PACK.CASE_CNT, LA.EXTERNAL_LOT, OD.REMARK, VENDOR.VENDOR_ID, VENDOR.COMPANY, LA.PRODUCE_DATE, LA.LOT_ATTR09, LAC.DESCR");
            strSql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", OrderID) };
            List<PrintCommoditySignoffDetailInfo> printCommoditySignoList = new List<PrintCommoditySignoffDetailInfo>();
            PrintCommoditySignoffDetailInfo printCommoditySigno = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.ToString(), paras))
            {
                while (reader.Read())
                {
                    printCommoditySigno = new PrintCommoditySignoffDetailInfo(reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["SKU_DESCR2"].ToString(), reader["UOM_ID"].ToString(), reader["UOM_DESCR"].ToString(), reader["PACK_ID"].ToString(), decimal.Parse((reader["ORIGINAL_QTY"].ToString() == "") ? "0" : reader["ORIGINAL_QTY"].ToString()), decimal.Parse((reader["CASE_ORIGINAL_QTY"].ToString() == "") ? "0" : reader["CASE_ORIGINAL_QTY"].ToString()), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), float.Parse((reader["GROSS_WGT"].ToString() == "") ? "0" : reader["GROSS_WGT"].ToString()), float.Parse((reader["CUBE"].ToString() == "") ? "0" : reader["CUBE"].ToString()), decimal.Parse((reader["CASE_QTY"].ToString() == "") ? "0" : reader["CASE_QTY"].ToString()), reader["PACK_UOM"].ToString(), reader["PACK_UOM_DESCR"].ToString(), decimal.Parse((reader["CASE_CNT"].ToString() == "") ? "0" : reader["CASE_CNT"].ToString()), reader["EXTERNAL_LOT"].ToString(), reader["REMARK"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : new DateTime(), reader["LOT_ATTR09"].ToString(), reader["SKU_PROPERTY"].ToString());
                    printCommoditySignoList.Add(printCommoditySigno);
                }
            }
            return printCommoditySignoList;
        }

        public DataSet GetPrintCommoditySignoffInfo(string orderId, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintCommoditySignoff");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintCommoditySignoff");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderId) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintCycleCountByReleaseID(string releaseID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = string.Empty;
            sql = ("SELECT CCC.INCL_ZERO_COUNT FROM @_@CYCLE_COUNT_RELEASE CCR LEFT JOIN @_@CYCLE_COUNT_CRITERIA CCC ON CCC.CC_CRITERIA_ID = CCR.CC_CRITERIA_ID WHERE RELEASE_ID = '" + releaseID + "'").Replace("@_@", tempPrintReportsInfo.WhLoignID);
            object is_INCL_ZERO_COUNT = DBHelper.ExecuteScalar(CommandType.Text, sql);
            sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintCycleCount");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintCycleCount");
            }
            sql = string.Format(sql, (is_INCL_ZERO_COUNT.ToString().ToUpper() == "N") ? " AND SYS_QTY != 0 " : "").Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RELEASE_ID", releaseID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public List<PrintCycleCountTagListInfo> GetPrintCycleCountTagListInfo(string releaseID, string OnlyNonEmptyLoc, PrintReportsInfo tempPrintReportsInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT TAG_NO,OWNER_ID,LOC,SKU_ID,LOT,LPN_ID,SYS_QTY,COUNT_QTY ");
            strSql.AppendFormat("   FROM {0}CYCLE_COUNT_TAG  ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat("  WHERE RELEASE_ID='{0}'", releaseID);
            if (!string.IsNullOrEmpty(OnlyNonEmptyLoc) && (OnlyNonEmptyLoc.ToUpper() == "N"))
            {
                strSql.AppendFormat("  AND SYS_QTY !=0", new object[0]);
            }
            strSql.Append("  ORDER BY TAG_NO ASC ");
            List<PrintCycleCountTagListInfo> printCycleCountTagListInfo = new List<PrintCycleCountTagListInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    PrintCycleCountTagListInfo printCycleCountTagInfo = new PrintCycleCountTagListInfo(Convert.ToInt32(reader["TAG_NO"]), reader["OWNER_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["SKU_ID"].ToString(), Convert.ToDecimal(reader["SYS_QTY"]), Convert.ToDecimal(reader["COUNT_QTY"]));
                    printCycleCountTagListInfo.Add(printCycleCountTagInfo);
                }
            }
            return printCycleCountTagListInfo;
        }

        public DataSet GetPrintImportReceipt(string receiptID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = string.Empty;
            sql = ("SELECT IS_PALLETIZED FROM @_@RECEIPT WHERE RECEIPT_ID = '" + receiptID + "'").Replace("@_@", tempPrintReportsInfo.WhLoignID);
            if (DBHelper.ExecuteScalar(CommandType.Text, sql).ToString().ToUpper() == "N")
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintImportReceipt");
                if (string.IsNullOrEmpty(sql))
                {
                    sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintImportReceipt");
                }
            }
            else
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintImportReceipt_Palletized");
                if (string.IsNullOrEmpty(sql))
                {
                    sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintImportReceipt_Palletized");
                }
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintInventoryAdjustment(string adjustmentID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = string.Empty;
            sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintInventoryAdjustment");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintInventoryAdjustment");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ADJUSTMENT_ID", adjustmentID), new SqlParameter("@LANGUAGE", tempPrintReportsInfo.UserLanguage) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintKittingWorkReportInfo(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintKittingWork");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintKittingWork");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintLoading(string loadID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = string.Empty;
            sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintLoading");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintLoading");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOAD_ID", loadID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintLpnLabelInfo(string Lpns, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintLpnLabel");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintLpnLabel");
            }
            sql = string.Format(sql, Lpns).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetPrintMergePickListDetailReportInfo(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintMergePickList");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintMergePickList");
            }
            sql = string.Format(sql, orderID).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public List<PrintOrderMovementDetailInfo> GetPrintOrderMovementDetailInfoList(string movementID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = "  SELECT A.ROWID,A.WH_ID,A.MOVEMENT_ID,A.LINE_ID, A.OWNER_ID,A.SKU_ID,A.LOT,A.FROM_LPN_ID,A.FROM_QTY,A.FROM_LOC,A.REASON_CODE,A.REMARK, A.CREATED_BY, A.CREATED_DATE, A.UPDATED_BY, A.UPDATED_DATE,B.QTY-B.ALLOCATED_QTY-B.PICKED_QTY-B.EXPECTED_QTY- B.PICKINPROCESS_QTY AS AVAILABLEQTY,B.ALLOCATED_QTY AS ALLOCATEDQTY,A.TO_LPN_ID,A.TO_LOC,A.QTY, A.PACK_ID,A.UOM_ID,A.STATUS,UOM.UOM_DESCR,C.DESCR AS SKU_DESCR,   D.VENDOR_ID,D.EXTERNAL_LOT,D.PRODUCE_DATE,D.EXPIRY_DATE,D.RECEIPT_DATE, D.LOT_ATTR01 ,D.LOT_ATTR02 ,D.LOT_ATTR03 ,D.LOT_ATTR04 ,D.LOT_ATTR05,D.LOT_ATTR06 ,D.LOT_ATTR07 ,D.LOT_ATTR08,D.LOT_ATTR09,D.LOT_ATTR10,B.STATUS AS HOLD_STATUS, LAC.DESCR AS SKU_PROPERTY    FROM @_@MOVEMENT_DETAIL A  LEFT JOIN  @_@INV_LOT_LOC_LPN  B ON A.FROM_LOC = B.LOC AND A.LOT=B.LOT AND A.FROM_LPN_ID=LPN_ID    LEFT JOIN @_@SKU C ON A.SKU_ID = C.SKU_ID AND A.OWNER_ID = C.OWNER_ID    LEFT JOIN @_@UOM UOM ON A.UOM_ID = UOM.UOM_ID   LEFT JOIN @_@LOT_ATTRIBUTE D ON A.LOT = D.LOT   LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = A.STATUS AND SCD.SYS_CODE_TYPE = 'MovementStatus'   LEFT JOIN @_@LOT_ATTRIBUTE_CODE AS LAC ON D.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'  WHERE A.MOVEMENT_ID = @MOVEMENT_ID ";
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", movementID) };
            List<PrintOrderMovementDetailInfo> printOrderMovementDetailInfos = new List<PrintOrderMovementDetailInfo>();
            PrintOrderMovementDetailInfo printOrderMovementDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    printOrderMovementDetailInfo = new PrintOrderMovementDetailInfo(reader["SKU_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["UOM_ID"].ToString(), reader["UOM_DESCR"].ToString(), reader["FROM_LOC"].ToString(), reader["TO_LOC"].ToString(), decimal.Parse((reader["AVAILABLEQTY"].ToString() == "") ? "0" : reader["AVAILABLEQTY"].ToString()), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["LOT"].ToString(), reader["EXTERNAL_LOT"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : new DateTime(), reader["LOT_ATTR09"].ToString(), reader["SKU_PROPERTY"].ToString());
                    printOrderMovementDetailInfos.Add(printOrderMovementDetailInfo);
                }
            }
            return printOrderMovementDetailInfos;
        }

        public DataSet GetPrintOrderMovementInfo(string movementID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintOrderMovement");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintOrderMovement");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", movementID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public List<PrintOutboundDetailInfo> GetPrintOutboundDetailInfoList(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT OD.LINE_ID,OD.SKU_ID,SKU.DESCR,OD.ORIGINAL_QTY,UOM.UOM_ID,UOM.UOM_DESCR,OD.PACK_ID,OD.SHIPPED_QTY,(SKU.GROSS_WGT * OD.ORIGINAL_QTY) AS GROSS_WGT,(SKU.[CUBE] * OD.ORIGINAL_QTY) AS CUBE,OD.REMARK,OD.EXTERNAL_LOT, OD.LOT_ATTR01, OD.LOT_ATTR02, OD.LOT_ATTR03, OD.LOT_ATTR04, OD.LOT_ATTR05, OD.LOT_ATTR06, OD.LOT_ATTR07, OD.LOT_ATTR08, OD.LOT_ATTR09, OD.LOT_ATTR10, OD.PRODUCE_DATE, OD.[EXPIRY_DATE],OD.VENDOR_ID, VENDOR.[DESCRIPTION] AS VENDOR_NAME, LAC.DESCR AS SKU_PROPERTY");
            strSql.AppendFormat(" FROM {0}ORDER_DETAIL OD LEFT JOIN {0}SKU SKU ON OD.SKU_ID = SKU.SKU_ID AND OD.OWNER_ID = SKU.OWNER_ID LEFT JOIN {0}UOM UOM ON OD.UOM_ID = UOM.UOM_ID LEFT JOIN {0}VENDOR VENDOR ON OD.VENDOR_ID = VENDOR.VENDOR_ID ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" LEFT JOIN {0}LOT_ATTRIBUTE_CODE AS LAC ON OD.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'", tempPrintReportsInfo.WhLoignID);
            strSql.Append(" WHERE OD.ORDER_ID =  @ORDER_ID");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            List<PrintOutboundDetailInfo> printOutboundDetailInfoList = new List<PrintOutboundDetailInfo>();
            PrintOutboundDetailInfo printOutboundDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.ToString(), paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    printOutboundDetailInfo = new PrintOutboundDetailInfo(Convert.ToInt32(reader["LINE_ID"]), reader["SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["UOM_ID"].ToString(), reader["UOM_DESCR"].ToString(), reader["PACK_ID"].ToString(), decimal.Parse((reader["ORIGINAL_QTY"].ToString() == "") ? "0" : reader["ORIGINAL_QTY"].ToString()), decimal.Parse((reader["SHIPPED_QTY"].ToString() == "") ? "0" : reader["SHIPPED_QTY"].ToString()), float.Parse((reader["GROSS_WGT"].ToString() == "") ? "0" : reader["GROSS_WGT"].ToString()), float.Parse((reader["CUBE"].ToString() == "") ? "0" : reader["CUBE"].ToString()), reader["REMARK"].ToString(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_PROPERTY"].ToString());
                    printOutboundDetailInfoList.Add(printOutboundDetailInfo);
                }
            }
            return printOutboundDetailInfoList;
        }

        public DataSet GetPrintOutboundInfo(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintOutbound");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintOutbound");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public List<PrintOutboundListDetailInfo> GetPrintOutboundListDetailInfoList(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT PD.ORDER_ID, PD.SKU_ID, PD.ALT_SKU_ID, SKU.DESCR AS SKU_DESCR, SKU.DESCR2 AS SKU_DESCR2, PD.UOM_ID, UOM.UOM_DESCR, PD.PACK_ID, SUM(PD.QTY) AS QTY, SUM(PD.QTY) * SKU.GROSS_WGT AS GROSS_WGT, SUM(PD.QTY) * SKU.[CUBE] AS CUBE, PACK.PACK_UOM3 AS PACK_UOM, UOM_PACK.UOM_DESCR AS PACK_UOM_DESCR, PACK.CASE_CNT, SUM(PD.QTY) / (CASE PACK.CASE_CNT WHEN 0 THEN 1 ELSE PACK.CASE_CNT END) AS CASE_QTY, LA.EXTERNAL_LOT, OD.REMARK, VENDOR.VENDOR_ID, VENDOR.COMPANY AS VENDOR_NAME, LA.PRODUCE_DATE, LA.LOT_ATTR09, LAC.DESCR AS SKU_PROPERTY");
            strSql.Append(" FROM @_@PICK_DETAIL PD LEFT JOIN @_@ORDER_DETAIL OD ON OD.ORDER_ID = PD.ORDER_ID AND OD.LINE_ID = PD.ORDER_LINE_ID");
            strSql.Append(" LEFT JOIN @_@SKU SKU ON SKU.SKU_ID = PD.SKU_ID AND SKU.OWNER_ID = PD.OWNER_ID");
            strSql.Append(" LEFT JOIN @_@UOM UOM ON UOM.UOM_ID = PD.UOM_ID");
            strSql.Append(" LEFT JOIN @_@VENDOR VENDOR ON OD.VENDOR_ID = VENDOR.VENDOR_ID");
            strSql.Append(" LEFT JOIN @_@PACK PACK ON PACK.PACK_ID = PD.PACK_ID");
            strSql.Append(" LEFT JOIN @_@UOM UOM_PACK ON UOM_PACK.UOM_ID = PACK.PACK_UOM3");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.LOT = PD.LOT");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE_CODE AS LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'");
            strSql.Append(" WHERE PD.[STATUS] = 'Shipped' AND PD.ORDER_ID = @ORDER_ID ");
            strSql.Append(" GROUP BY PD.ORDER_ID, PD.SKU_ID, PD.ALT_SKU_ID, SKU.DESCR, SKU.DESCR2, PD.UOM_ID, UOM.UOM_DESCR, PD.PACK_ID, SKU.GROSS_WGT, SKU.[CUBE], PACK.PACK_UOM3, UOM_PACK.UOM_DESCR, PACK.CASE_CNT, LA.EXTERNAL_LOT, OD.REMARK, VENDOR.VENDOR_ID, VENDOR.COMPANY, LA.PRODUCE_DATE, LA.LOT_ATTR09, LAC.DESCR");
            strSql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            List<PrintOutboundListDetailInfo> printOutboundListDetailInfoList = new List<PrintOutboundListDetailInfo>();
            PrintOutboundListDetailInfo printOutboundListDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.ToString(), paras))
            {
                while (reader.Read())
                {
                    printOutboundListDetailInfo = new PrintOutboundListDetailInfo(reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["SKU_DESCR2"].ToString(), reader["UOM_ID"].ToString(), reader["UOM_DESCR"].ToString(), reader["PACK_ID"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), float.Parse((reader["GROSS_WGT"].ToString() == "") ? "0" : reader["GROSS_WGT"].ToString()), float.Parse((reader["CUBE"].ToString() == "") ? "0" : reader["CUBE"].ToString()), decimal.Parse((reader["CASE_QTY"].ToString() == "") ? "0" : reader["CASE_QTY"].ToString()), reader["PACK_UOM"].ToString(), reader["PACK_UOM_DESCR"].ToString(), decimal.Parse((reader["CASE_CNT"].ToString() == "") ? "0" : reader["CASE_CNT"].ToString()), reader["EXTERNAL_LOT"].ToString(), reader["REMARK"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : new DateTime(), reader["LOT_ATTR09"].ToString(), reader["SKU_PROPERTY"].ToString());
                    printOutboundListDetailInfoList.Add(printOutboundListDetailInfo);
                }
            }
            return printOutboundListDetailInfoList;
        }

        public DataSet GetPrintOutboundListInfo(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintOutboundList");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintOutboundList");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintPaymentBillByMovementID(string movementID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintPaymentBillByMovementID");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintPaymentBillByMovementID");
            }
            sql = string.Format(sql, movementID).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetPrintPaymentBillByOrderID(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintPaymentBillByOrderID");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintPaymentBillByOrderID");
            }
            sql = string.Format(sql, orderID).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetPrintPaymentBillByReceiptID(string receiptID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintPaymentBillByReceiptID");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintPaymentBillByReceiptID");
            }
            sql = string.Format(sql, receiptID).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetPrintPCMaintenanceList(string PhysicalRequestID, string OnlyNonEmptyLoc, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintPCMaintenance");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintPCMaintenance");
            }
            sql = string.Format(sql, (OnlyNonEmptyLoc.ToUpper() == "TRUE") ? " AND SYS_QTY != 0 " : "").Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", PhysicalRequestID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public List<PrintPCMaintenanceListDetailInfo> GetPrintPCMaintenanceListDetailInfo(string PhysicalRequestID, string OnlyNonEmptyLoc, PrintReportsInfo tempPrintReportsInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT TAG_NO,OWNER_ID,LOC,SKU_ID,LOT,LPN_ID,UOM_ID,SYS_QTY,COUNT_QTY ");
            strSql.AppendFormat("   FROM {0}PHYSICAL_TAG ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat("  WHERE PHYSICAL_REQUEST_ID='{0}'", PhysicalRequestID);
            if (!string.IsNullOrEmpty(OnlyNonEmptyLoc) && (OnlyNonEmptyLoc.ToUpper() == "TRUE"))
            {
                strSql.AppendFormat("  AND SYS_QTY !=0", new object[0]);
            }
            strSql.Append("  ORDER BY TAG_NO ASC ");
            List<PrintPCMaintenanceListDetailInfo> printpcMaintenanceList = new List<PrintPCMaintenanceListDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    PrintPCMaintenanceListDetailInfo printPCMaintenanceDetailInfo = new PrintPCMaintenanceListDetailInfo(Convert.ToInt32(reader["TAG_NO"]), reader["OWNER_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["SKU_ID"].ToString(), reader["UOM_ID"].ToString(), Convert.ToDecimal(reader["SYS_QTY"]), Convert.ToDecimal(reader["COUNT_QTY"]));
                    printpcMaintenanceList.Add(printPCMaintenanceDetailInfo);
                }
            }
            return printpcMaintenanceList;
        }

        public DataSet GetPrintPCReview(string PhysicalRequestID, string OnlyNonEmptyLoc, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintPCReview");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintPCReview");
            }
            sql = string.Format(sql, (OnlyNonEmptyLoc.ToUpper() == "TRUE") ? " AND SYS_QTY != 0 " : "").Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", PhysicalRequestID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintPickDivideReport(string waveID, string orderIDs, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintPickDivide");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintPickDivide");
            }
            sql = string.Format(sql, orderIDs).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql);
            ds.Tables[0].Columns.Add("DIVIDE_SKU", typeof(string));
            sql = string.Format("SELECT * FROM {0}WAVE_DETAIL wd WHERE wd.WAVE_ID = '{1}' AND wd.ORDER_ID IN ({2}) ORDER BY wd.CREATED_DATE", tempPrintReportsInfo.WhLoignID, waveID, orderIDs);
            DataSet ds_order_sn = DBHelper.ExecuteDataSet(CommandType.Text, sql);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                StringBuilder strSQL = new StringBuilder();
                strSQL.Append(" SELECT PD.ORDER_ID, WD.CREATED_DATE, CAST(SUM(PD.QTY) AS INT) AS QTY ");
                strSQL.AppendFormat(" FROM {0}PICK_DETAIL PD LEFT JOIN {0}WAVE_DETAIL WD ON WD.ORDER_ID = PD.ORDER_ID ", tempPrintReportsInfo.WhLoignID);
                strSQL.AppendFormat(" LEFT JOIN {0}SKU SKU ON PD.SKU_ID = SKU.SKU_ID AND PD.OWNER_ID = SKU.OWNER_ID ", tempPrintReportsInfo.WhLoignID);
                strSQL.AppendFormat(" LEFT JOIN {0}UOM UOM ON PD.UOM_ID = UOM.UOM_ID LEFT JOIN {0}PACK PACK ON PD.PACK_ID = PACK.PACK_ID ", tempPrintReportsInfo.WhLoignID);
                strSQL.AppendFormat(" LEFT JOIN {0}LOT_ATTRIBUTE LA ON  LA.LOT = PD.LOT ", tempPrintReportsInfo.WhLoignID);
                strSQL.AppendFormat(" WHERE PD.STATUS IN ('NEW', 'RELEASED') AND PD.ORDER_ID IN ({0}) AND WD.WAVE_ID = '{1}' ", orderIDs, waveID);
                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["SKU_ID"].ToString()))
                {
                    strSQL.AppendFormat(" AND PD.SKU_ID = '{0}' ", ds.Tables[0].Rows[i]["SKU_ID"].ToString());
                }
                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["ALT_SKU_ID"].ToString()))
                {
                    strSQL.AppendFormat(" AND PD.ALT_SKU_ID = '{0}' ", ds.Tables[0].Rows[i]["ALT_SKU_ID"].ToString());
                }
                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["FROM_LOC"].ToString()))
                {
                    strSQL.AppendFormat(" AND PD.LOC = '{0}' ", ds.Tables[0].Rows[i]["FROM_LOC"].ToString());
                }
                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["TO_LOC"].ToString()))
                {
                    strSQL.AppendFormat(" AND PD.TO_LOC = '{0}' ", ds.Tables[0].Rows[i]["TO_LOC"].ToString());
                }
                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["UOM_ID"].ToString()))
                {
                    strSQL.AppendFormat(" AND PD.UOM_ID = '{0}' ", ds.Tables[0].Rows[i]["UOM_ID"].ToString());
                }
                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["PACK_ID"].ToString()))
                {
                    strSQL.AppendFormat(" AND PD.PACK_ID = '{0}' ", ds.Tables[0].Rows[i]["PACK_ID"].ToString());
                }
                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["LPN_ID"].ToString()))
                {
                    strSQL.AppendFormat(" AND PD.LPN_ID = '{0}' ", ds.Tables[0].Rows[i]["LPN_ID"].ToString());
                }
                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["LOT"].ToString()))
                {
                    strSQL.AppendFormat(" AND PD.LOT = '{0}' ", ds.Tables[0].Rows[i]["LOT"].ToString());
                }
                strSQL.Append(" GROUP BY PD.ORDER_ID, WD.CREATED_DATE HAVING SUM(PD.QTY) > 0 ORDER BY WD.CREATED_DATE ");
                using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSQL.ToString()))
                {
                    string divide_sku = string.Empty;
                    while (reader.Read())
                    {
                        string orderid = reader["ORDER_ID"].ToString();
                        string qty = reader["QTY"].ToString();
                        for (int sn = 0; sn < ds_order_sn.Tables[0].Rows.Count; sn++)
                        {
                            if (string.Compare(orderid, ds_order_sn.Tables[0].Rows[sn]["ORDER_ID"].ToString(), true) == 0)
                            {
                                int CS00002 = sn + 1;
                                divide_sku = divide_sku + (string.IsNullOrEmpty(divide_sku) ? "" : ",") + string.Format("({0})*{1}", CS00002.ToString(), qty);
                                break;
                            }
                        }
                    }
                    ds.Tables[0].Rows[i].SetField<string>("DIVIDE_SKU", divide_sku);
                }
            }
            return ds;
        }

        public List<PrintPickListDetailReportInfo> GetPrintPickListDetailReportInfo(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT PD.SKU_ID,PD.ALT_SKU_ID, SKU.DESCR,SKU.DESCR2,SKU.UDF2 AS SKU_UDF2,PD.LOC AS FROM_LOC,PD.TO_LOC,UOM.UOM_DESCR,Sum(PD.QTY) AS QTY, SUM(PD.QTY)  * SUM(SKU.GROSS_WGT) AS GROSS_WGT, SUM(PD.QTY) * SUM(SKU.[CUBE]) AS [CUBE],");
            strSql.Append("  PD.LPN_ID,'' AS FINAL_LOC,'' AS REMARK,PD.STATUS, SKU.PACK_ID,UOM.UOM_ID,PACK.PACK_UOM3, PACK.CASE_CNT,");
            strSql.Append(" LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,");
            strSql.Append(" LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LA.LOT_ATTR09,LA.LOT_ATTR10,");
            strSql.Append(" LA.EXTERNAL_LOT,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.VENDOR_ID,LAC.DESCR AS SKU_PROPERTY");
            strSql.AppendFormat(" FROM {0}PICK_DETAIL PD ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" LEFT JOIN {0}SKU SKU ON PD.SKU_ID = SKU.SKU_ID AND PD.OWNER_ID = SKU.OWNER_ID ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" LEFT JOIN {0}UOM UOM ON PD.UOM_ID = UOM.UOM_ID  ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" LEFT JOIN {0}PACK PACK ON PD.PACK_ID = PACK.PACK_ID ", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" LEFT JOIN {0}LOT_ATTRIBUTE LA ON LA.LOT = PD.LOT", tempPrintReportsInfo.WhLoignID);
            strSql.AppendFormat(" LEFT JOIN {0}LOT_ATTRIBUTE_CODE AS LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'", tempPrintReportsInfo.WhLoignID);
            strSql.Append(" WHERE PD.ORDER_ID = @ORDER_ID   ");
            strSql.Append(" GROUP BY  PD.SKU_ID,PD.ALT_SKU_ID, SKU.DESCR,SKU.DESCR2,SKU.UDF2 ,PD.LOC ,PD.TO_LOC,UOM.UOM_DESCR,PD.LPN_ID,PD.STATUS, SKU.PACK_ID,UOM.UOM_ID,PACK.PACK_UOM3, PACK.CASE_CNT,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LA.LOT_ATTR09,LA.LOT_ATTR10,LA.EXTERNAL_LOT,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.VENDOR_ID,LAC.DESCR  ORDER BY PD.LOC,PD.SKU_ID DESC ");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            List<PrintPickListDetailReportInfo> printPickListDetailReportInfoList = new List<PrintPickListDetailReportInfo>();
            PrintPickListDetailReportInfo printPickListDetailReportInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.ToString(), paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    printPickListDetailReportInfo = new PrintPickListDetailReportInfo(reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["DESCR2"].ToString(), reader["FROM_LOC"].ToString(), reader["TO_LOC"].ToString(), reader["UOM_DESCR"].ToString(), decimal.Parse(reader["QTY"].ToString()), reader["LPN_ID"].ToString(), reader["FINAL_LOC"].ToString(), reader["REMARK"].ToString(), reader["STATUS"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_UOM3"].ToString(), decimal.Parse(reader["CASE_CNT"].ToString()), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), reader["EXTERNAL_LOT"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), reader["VENDOR_ID"].ToString(), reader["SKU_PROPERTY"].ToString(), reader["SKU_UDF2"].ToString(), decimal.Parse(reader["GROSS_WGT"].ToString()), decimal.Parse(reader["CUBE"].ToString()));
                    printPickListDetailReportInfoList.Add(printPickListDetailReportInfo);
                }
            }
            return printPickListDetailReportInfoList;
        }

        public DataSet GetPrintPickListReportInfo(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintPickList");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintPickList");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintPickReportInfo(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintPick");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintPick");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintPickWorkReportInfo(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintPickWork");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintPickWork");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public List<PrintPurchaseOrderDetailInfo> GetPrintPurchaseOrderDetailInfoList(string poID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = "  SELECT POD.SKU_ID,SKU.DESCR,SKU.DESCR2,UOM.UOM_ID,UOM.UOM_DESCR,POD.ORDERED_QTY,POD.TOTAL_GROSS_WEIGHT,POD.TOTAL_CUBE,POD.REMARK,POD.PACK_ID,POD.UNIT_PRICE,POD.TOTAL_PRICE,  (SELECT TOP 1 ALT_SKU_ID FROM @_@ALTERNATE_SKU WHERE SKU_ID = SKU.SKU_ID) AS ALT_SKU_ID,SKU.UDF2  FROM @_@PURCHASE_ORDER_DETAIL POD  LEFT JOIN @_@SKU SKU ON POD.SKU_ID = SKU.SKU_ID AND POD.OWNER_ID = SKU.OWNER_ID LEFT JOIN @_@UOM UOM ON POD.UOM_ID = UOM.UOM_ID WHERE POD.PO_ID = @PO_ID ";
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", poID) };
            List<PrintPurchaseOrderDetailInfo> printPurchaseOrderDetailInfos = new List<PrintPurchaseOrderDetailInfo>();
            PrintPurchaseOrderDetailInfo printPurchaseOrderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    printPurchaseOrderDetailInfo = new PrintPurchaseOrderDetailInfo(reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["DESCR2"].ToString(), reader["UOM_ID"].ToString(), reader["UOM_DESCR"].ToString(), decimal.Parse((reader["ORDERED_QTY"].ToString() == "") ? "0" : reader["ORDERED_QTY"].ToString()), float.Parse((reader["TOTAL_GROSS_WEIGHT"].ToString() == "") ? "0" : reader["TOTAL_GROSS_WEIGHT"].ToString()), float.Parse((reader["TOTAL_CUBE"].ToString() == "") ? "0" : reader["TOTAL_CUBE"].ToString()), reader["REMARK"].ToString(), reader["PACK_ID"].ToString(), decimal.Parse((reader["UNIT_PRICE"].ToString() == "") ? "0" : reader["UNIT_PRICE"].ToString()), decimal.Parse((reader["TOTAL_PRICE"].ToString() == "") ? "0" : reader["TOTAL_PRICE"].ToString()), reader["UDF2"].ToString());
                    printPurchaseOrderDetailInfos.Add(printPurchaseOrderDetailInfo);
                }
            }
            return printPurchaseOrderDetailInfos;
        }

        public DataSet GetPrintPurchaseOrderInfo(string poID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintPurchaseOrder");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintPurchaseOrder");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", poID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public List<PrintPutawayDetailInfo> GetPrintPutawayDetailInfoList(string receiptID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = "  SELECT TD.SKU_ID,SKU.DESCR,TD.FROM_LOC,TD.TO_LOC,UOM.UOM_DESCR,TD.QTY,TD.TO_LPN_ID,TD.LOT,TD.STATUS,SKU.PACK_ID,UOM.UOM_ID  FROM @_@TASK_DETAIL TD LEFT JOIN @_@SKU SKU ON TD.OWNER_ID = SKU.OWNER_ID AND TD.SKU_ID = SKU.SKU_ID LEFT JOIN @_@UOM UOM ON TD.UOM_ID = UOM.UOM_ID WHERE TD.TASK_TYPE = 'PA' AND TD.DOC_ID = @RECEIPT_ID ";
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            List<PrintPutawayDetailInfo> printPutAwayDetailInfos = new List<PrintPutawayDetailInfo>();
            PrintPutawayDetailInfo printPutAwayDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    printPutAwayDetailInfo = new PrintPutawayDetailInfo(reader["SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["FROM_LOC"].ToString(), reader["TO_LOC"].ToString(), reader["UOM_DESCR"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["TO_LPN_ID"].ToString(), reader["LOT"].ToString(), "", reader["STATUS"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString());
                    printPutAwayDetailInfos.Add(printPutAwayDetailInfo);
                }
            }
            return printPutAwayDetailInfos;
        }

        public DataSet GetPrintPutawayReportInfo(string receiptID, List<string> taskDetailID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintPutaway");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintPutaway");
            }
            string sqlTaskDetailID = "";
            foreach (string taskID in taskDetailID)
            {
                sqlTaskDetailID = sqlTaskDetailID + string.Format("'{0}',", taskID);
            }
            sqlTaskDetailID = sqlTaskDetailID.Substring(0, sqlTaskDetailID.Length - 1);
            if (!string.IsNullOrEmpty(sqlTaskDetailID))
            {
                sql = string.Format(sql, sqlTaskDetailID);
            }
            else
            {
                sql = string.Format(sql, "SELECT TASK_DETAIL_ID FROM @_@TASK_DETAIL WHERE TASK_TYPE = 'PA' AND DOC_ID = @RECEIPT_ID");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintQCReportInfo(string receiptID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = string.Empty;
            sql = ("SELECT IS_PALLETIZED FROM @_@RECEIPT WHERE RECEIPT_ID = '" + receiptID + "'").Replace("@_@", tempPrintReportsInfo.WhLoignID);
            if (DBHelper.ExecuteScalar(CommandType.Text, sql).ToString().ToUpper() == "N")
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintQCReport");
                if (string.IsNullOrEmpty(sql))
                {
                    sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintQCReport");
                }
            }
            else
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintQCReport_Palletized");
                if (string.IsNullOrEmpty(sql))
                {
                    sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintQCReport_Palletized");
                }
            }
            sql = string.Format(sql, receiptID).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public List<PrintReceiptDetailInfo> GetPrintReceiptDetailInfoList(string receiptID, bool isPalletized, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = string.Empty;
            if (isPalletized)
            {
                sql = "  SELECT RD.SKU_ID,RD.ALT_SKU_ID,SKU.DESCR,SKU.DESCR2,UOM.UOM_ID,UOM.UOM_DESCR,RD.EXPECTED_QTY,RD.RECEIVED_QTY,RD.GROSS_WGT,RD.[CUBE],RD.CONDITION_CODE,RD.TO_LOC,RD.LPN_ID,RD.EXTERNAL_LOT,RD.REMARK,RD.PACK_ID,RD.VENDOR_ID, RD.LOT_ATTR01, RD.LOT_ATTR02, RD.LOT_ATTR03, RD.LOT_ATTR04, RD.LOT_ATTR05, RD.LOT_ATTR06, RD.LOT_ATTR07, RD.LOT_ATTR08, RD.LOT_ATTR09, RD.LOT_ATTR10, RD.PRODUCE_DATE,RD.[EXPIRY_DATE], VENDOR.COMPANY AS VENDOR_NAME, LAC.DESCR AS SKU_PROPERTY,SKU.UDF2 AS SKUUDF2,SKU.SHELF_LIFE_ON_RECEIVING FROM @_@RECEIPT_DETAIL_LPN RD  LEFT JOIN @_@SKU SKU ON RD.SKU_ID = SKU.SKU_ID AND RD.OWNER_ID = SKU.OWNER_ID LEFT JOIN @_@UOM UOM ON RD.UOM_ID = UOM.UOM_ID LEFT JOIN @_@VENDOR VENDOR ON RD.VENDOR_ID = VENDOR.VENDOR_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE AS LAC ON RD.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' WHERE RD.RECEIPT_ID = @RECEIPT_ID ";
            }
            else
            {
                sql = "  SELECT RD.SKU_ID,RD.ALT_SKU_ID,SKU.DESCR,SKU.DESCR2,UOM.UOM_ID,UOM.UOM_DESCR,RD.EXPECTED_QTY,RD.RECEIVED_QTY,RD.GROSS_WGT,RD.[CUBE],RD.CONDITION_CODE,RD.TO_LOC,RD.LPN_ID,RD.EXTERNAL_LOT,RD.REMARK,RD.PACK_ID,RD.VENDOR_ID, RD.LOT_ATTR01, RD.LOT_ATTR02, RD.LOT_ATTR03, RD.LOT_ATTR04, RD.LOT_ATTR05, RD.LOT_ATTR06, RD.LOT_ATTR07, RD.LOT_ATTR08, RD.LOT_ATTR09, RD.LOT_ATTR10, RD.PRODUCE_DATE,RD.[EXPIRY_DATE], VENDOR.COMPANY AS VENDOR_NAME, LAC.DESCR AS SKU_PROPERTY,SKU.UDF2 AS SKUUDF2,SKU.PACK_ID,SKU.SHELF_LIFE_ON_RECEIVING FROM @_@RECEIPT_DETAIL RD  LEFT JOIN @_@SKU SKU ON RD.SKU_ID = SKU.SKU_ID AND RD.OWNER_ID = SKU.OWNER_ID LEFT JOIN @_@UOM UOM ON RD.UOM_ID = UOM.UOM_ID LEFT JOIN @_@VENDOR VENDOR ON RD.VENDOR_ID = VENDOR.VENDOR_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE AS LAC ON RD.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' WHERE RD.RECEIPT_ID = @RECEIPT_ID ";
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            List<PrintReceiptDetailInfo> printReceiptDetailInfos = new List<PrintReceiptDetailInfo>();
            PrintReceiptDetailInfo printReceiptDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00003;
                    printReceiptDetailInfo = new PrintReceiptDetailInfo(reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["DESCR2"].ToString(), reader["UOM_ID"].ToString(), reader["UOM_DESCR"].ToString(), decimal.Parse((reader["EXPECTED_QTY"].ToString() == "") ? "0" : reader["EXPECTED_QTY"].ToString()), decimal.Parse((reader["RECEIVED_QTY"].ToString() == "") ? "0" : reader["RECEIVED_QTY"].ToString()), float.Parse((reader["GROSS_WGT"].ToString() == "") ? "0" : reader["GROSS_WGT"].ToString()), float.Parse((reader["CUBE"].ToString() == "") ? "0" : reader["CUBE"].ToString()), reader["TO_LOC"].ToString(), reader["LPN_ID"].ToString(), reader["EXTERNAL_LOT"].ToString(), reader["REMARK"].ToString(), reader["PACK_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_PROPERTY"].ToString(), reader["PACK_ID"].ToString(), reader["SKUUDF2"].ToString(), int.Parse((reader["SHELF_LIFE_ON_RECEIVING"].ToString() == "") ? "0" : reader["SHELF_LIFE_ON_RECEIVING"].ToString()));
                    printReceiptDetailInfos.Add(printReceiptDetailInfo);
                }
            }
            return printReceiptDetailInfos;
        }

        public List<PrintReceiptHistoryDetailInfo> GetPrintReceiptHistoryDetailInfoList(string receiptID, bool isPalletized, PrintReportsInfo tempPrintReportsInfo)
        {
            StringBuilder strSql = new StringBuilder();
            if (isPalletized)
            {
                strSql.Append(" SELECT IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,S.DESCR,S.DESCR2,S.PACK_ID,IT.OWNER_ID,RD.ALT_SKU_ID,RD.REMARK,RD.VENDOR_ID,VENDOR.COMPANY AS VENDOR_NAME,IT.PACK_ID,IT.UOM_ID,UOM.UOM_DESCR,PACK.PACK_UOM3 AS PACK_UOM,UOM_PACK.UOM_DESCR AS PACK_UOM_DESCR,PACK.CASE_CNT,IT.QTY / (CASE PACK.CASE_CNT WHEN 0 THEN 1 ELSE PACK.CASE_CNT END) AS CASE_QTY,IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,IT.GROSS_WGT,IT.[CUBE],IT.LOT_ATTR09 AS HOLD_CODE, LAC.DESCR AS SKU_PROPERTY, IT.EXTERNAL_LOT,IT.PRODUCE_DATE,SUM(ISNULL(TD.QTY, 0)) AS COMPLETED_QTY,IT.QTY -SUM(ISNULL(TD.QTY, 0)) - SUM(ISNULL(PD.QTY, 0)) AS REMAIN_QTY,SUM(ISNULL(PD.QTY, 0)) AS ALLOCATED_QTY, IT.TO_LOC,IT.EXPIRY_DATE ");
                strSql.Append(" FROM @_@INV_TRANS IT LEFT JOIN @_@RECEIPT_DETAIL_LPN RD ON IT.RECEIPT_ID = RD.RECEIPT_ID AND IT.RECEIPT_LINE_ID = RD.RECEIPT_LINE_ID");
                strSql.Append("  LEFT JOIN @_@SKU S ON S.SKU_ID = RD.SKU_ID AND S.OWNER_ID = RD.OWNER_ID");
                strSql.Append("  LEFT JOIN @_@UOM UOM ON UOM.UOM_ID = IT.UOM_ID");
                strSql.Append("  LEFT JOIN @_@VENDOR VENDOR ON VENDOR.VENDOR_ID = RD.VENDOR_ID");
                strSql.Append("  LEFT JOIN @_@PACK PACK ON PACK.PACK_ID = IT.PACK_ID");
                strSql.Append("  LEFT JOIN @_@UOM UOM_PACK ON UOM_PACK.UOM_ID = PACK.PACK_UOM3");
                strSql.Append("  LEFT JOIN @_@TASK_DETAIL TD ON  IT.TRANS_ID = TD.FROM_TRANS_ID AND TD.STATUS = 'Completed'");
                strSql.Append("  LEFT JOIN @_@LOT_ATTRIBUTE LA ON  IT.LOT = LA.LOT AND LA.LOT_ATTR10 = @RECEIPT_ID");
                strSql.Append("  LEFT JOIN @_@PICK_DETAIL PD ON IT.LOT = PD.LOT AND IT.TO_LOC = PD.LOC AND IT.TO_LPN_ID = PD.LPN_ID AND PD.STATUS IN ('NEW', 'RELEASE', 'PICKED', 'SHIPPED')");
                strSql.Append("  LEFT JOIN @_@LOT_ATTRIBUTE_CODE AS LAC ON IT.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'");
                strSql.Append(" WHERE IT.TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.STATUS = 'OK' AND IT.RECEIPT_ID = @RECEIPT_ID");
                strSql.Append(" GROUP BY IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,S.SKU_ID,S.DESCR,S.DESCR2,S.PACK_ID,IT.OWNER_ID,RD.ALT_SKU_ID,RD.VENDOR_ID,RD.REMARK,VENDOR.COMPANY,IT.PACK_ID,IT.UOM_ID,UOM.UOM_DESCR,PACK.PACK_UOM3,UOM_PACK.UOM_DESCR,PACK.CASE_CNT,IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,IT.GROSS_WGT,IT.[CUBE],IT.LOT_ATTR09,LAC.DESCR,IT.EXTERNAL_LOT,IT.PRODUCE_DATE,TD.STATUS,IT.TO_LOC,IT.EXPIRY_DATE");
            }
            else
            {
                strSql.Append(" SELECT IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,S.DESCR,S.DESCR2,S.PACK_ID,IT.OWNER_ID,RD.ALT_SKU_ID,RD.REMARK,RD.VENDOR_ID,VENDOR.COMPANY AS VENDOR_NAME,IT.PACK_ID,IT.UOM_ID,UOM.UOM_DESCR,PACK.PACK_UOM3 AS PACK_UOM,UOM_PACK.UOM_DESCR AS PACK_UOM_DESCR,PACK.CASE_CNT,IT.QTY / (CASE PACK.CASE_CNT WHEN 0 THEN 1 ELSE PACK.CASE_CNT END) AS CASE_QTY,IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,IT.GROSS_WGT,IT.[CUBE],IT.LOT_ATTR09 AS HOLD_CODE, LAC.DESCR AS SKU_PROPERTY, IT.EXTERNAL_LOT,IT.PRODUCE_DATE,SUM(ISNULL(TD.QTY, 0)) AS COMPLETED_QTY,IT.QTY -SUM(ISNULL(TD.QTY, 0)) - SUM(ISNULL(PD.QTY, 0)) AS REMAIN_QTY,SUM(ISNULL(PD.QTY, 0)) AS ALLOCATED_QTY, IT.TO_LOC,IT.EXPIRY_DATE");
                strSql.Append(" FROM @_@INV_TRANS IT LEFT JOIN @_@RECEIPT_DETAIL RD ON IT.RECEIPT_ID = RD.RECEIPT_ID AND IT.RECEIPT_LINE_ID = RD.RECEIPT_LINE_ID");
                strSql.Append("  LEFT JOIN @_@SKU S ON S.SKU_ID = RD.SKU_ID AND S.OWNER_ID = RD.OWNER_ID");
                strSql.Append("  LEFT JOIN @_@UOM UOM ON UOM.UOM_ID = IT.UOM_ID");
                strSql.Append("  LEFT JOIN @_@VENDOR VENDOR ON VENDOR.VENDOR_ID = RD.VENDOR_ID");
                strSql.Append("  LEFT JOIN @_@PACK PACK ON PACK.PACK_ID = IT.PACK_ID");
                strSql.Append("  LEFT JOIN @_@UOM UOM_PACK ON UOM_PACK.UOM_ID = PACK.PACK_UOM3");
                strSql.Append("  LEFT JOIN @_@TASK_DETAIL TD ON  IT.TRANS_ID = TD.FROM_TRANS_ID AND TD.STATUS = 'Completed'");
                strSql.Append("  LEFT JOIN @_@LOT_ATTRIBUTE LA ON  IT.LOT = LA.LOT AND LA.LOT_ATTR10 = @RECEIPT_ID");
                strSql.Append("  LEFT JOIN @_@PICK_DETAIL PD ON IT.LOT = PD.LOT AND IT.TO_LOC = PD.LOC AND IT.TO_LPN_ID = PD.LPN_ID AND PD.STATUS IN ('NEW', 'RELEASE', 'PICKED', 'SHIPPED')");
                strSql.Append("  LEFT JOIN @_@LOT_ATTRIBUTE_CODE AS LAC ON IT.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性'");
                strSql.Append(" WHERE IT.TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.STATUS = 'OK' AND IT.RECEIPT_ID = @RECEIPT_ID");
                strSql.Append(" GROUP BY IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,S.SKU_ID,S.DESCR,S.DESCR2,S.PACK_ID,IT.OWNER_ID,RD.ALT_SKU_ID,RD.VENDOR_ID,RD.REMARK,VENDOR.COMPANY,IT.PACK_ID,IT.UOM_ID,UOM.UOM_DESCR,PACK.PACK_UOM3,UOM_PACK.UOM_DESCR,PACK.CASE_CNT,IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,IT.GROSS_WGT,IT.[CUBE],IT.LOT_ATTR09,LAC.DESCR,IT.EXTERNAL_LOT,IT.PRODUCE_DATE,TD.STATUS, IT.TO_LOC,IT.EXPIRY_DATE");
            }
            strSql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            List<PrintReceiptHistoryDetailInfo> printReceiptHistoryDetailInfos = new List<PrintReceiptHistoryDetailInfo>();
            PrintReceiptHistoryDetailInfo printReceiptHistoryDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.ToString(), paras))
            {
                while (reader.Read())
                {
                    DateTime CS00003;
                    printReceiptHistoryDetailInfo = new PrintReceiptHistoryDetailInfo(reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["DESCR2"].ToString(), reader["UOM_ID"].ToString(), reader["UOM_DESCR"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), float.Parse((reader["GROSS_WGT"].ToString() == "") ? "0" : reader["GROSS_WGT"].ToString()), float.Parse((reader["CUBE"].ToString() == "") ? "0" : reader["CUBE"].ToString()), decimal.Parse((reader["CASE_QTY"].ToString() == "") ? "0" : reader["CASE_QTY"].ToString()), reader["PACK_UOM"].ToString(), reader["PACK_UOM_DESCR"].ToString(), decimal.Parse((reader["CASE_CNT"].ToString() == "") ? "0" : reader["CASE_CNT"].ToString()), reader["TO_LPN_ID"].ToString(), reader["EXTERNAL_LOT"].ToString(), reader["REMARK"].ToString(), reader["PACK_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_PROPERTY"].ToString(), reader["TO_LOC"].ToString(), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()));
                    printReceiptHistoryDetailInfos.Add(printReceiptHistoryDetailInfo);
                }
            }
            return printReceiptHistoryDetailInfos;
        }

        public DataSet GetPrintReceiptHistoryInfo(string receiptID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = string.Empty;
            sql = ("SELECT IS_PALLETIZED FROM @_@RECEIPT WHERE RECEIPT_ID = '" + receiptID + "'").Replace("@_@", tempPrintReportsInfo.WhLoignID);
            if (DBHelper.ExecuteScalar(CommandType.Text, sql).ToString().ToUpper() == "N")
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintReceiptHistory");
                if (string.IsNullOrEmpty(sql))
                {
                    sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintReceiptHistory");
                }
            }
            else
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintReceiptHistory_Palletized");
                if (string.IsNullOrEmpty(sql))
                {
                    sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintReceiptHistory_Palletized");
                }
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintReceiptInfo(string receiptID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = string.Empty;
            sql = ("SELECT IS_PALLETIZED FROM @_@RECEIPT WHERE RECEIPT_ID = '" + receiptID + "'").Replace("@_@", tempPrintReportsInfo.WhLoignID);
            if (DBHelper.ExecuteScalar(CommandType.Text, sql).ToString().ToUpper() == "N")
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintReceipt");
                if (string.IsNullOrEmpty(sql))
                {
                    sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintReceipt");
                }
            }
            else
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintReceipt_Palletized");
                if (string.IsNullOrEmpty(sql))
                {
                    sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintReceipt_Palletized");
                }
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintReceivableBillByMovementID(string movementID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintReceivableBillByMovementID");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintReceivableBillByMovementID");
            }
            sql = string.Format(sql, movementID).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetPrintReceivableBillByOrderID(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintReceivableBillByOrderID");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintReceivableBillByOrderID");
            }
            sql = string.Format(sql, orderID).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetPrintReceivableBillByReceiptID(string receiptID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintReceivableBillByReceiptID");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintReceivableBillByReceiptID");
            }
            sql = string.Format(sql, receiptID).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public List<PrintSalesOrderDetailInfo> GetPrintSalesOrderDetailInfoList(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = "  SELECT SOD.SKU_ID, SOD.ALT_SKU_ID, SKU.DESCR, SKU.DESCR2, UOM.UOM_ID, UOM.UOM_DESCR, SOD.ORIGINAL_QTY, SOD.UNIT_PRICE, SOD.EXTENDED_PRICE, SOD.REMARK, SKU.PACK_ID  FROM @_@SALES_ORDER_DETAIL SOD  LEFT JOIN @_@SKU SKU ON SKU.SKU_ID = SOD.SKU_ID AND SKU.OWNER_ID = SOD.OWNER_ID  LEFT JOIN @_@UOM UOM ON UOM.UOM_ID = SOD.UOM_ID  WHERE SOD.ORDER_ID = @ORDER_ID ";
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            List<PrintSalesOrderDetailInfo> printSalesOrderDetailInfos = new List<PrintSalesOrderDetailInfo>();
            PrintSalesOrderDetailInfo printSalesOrderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    printSalesOrderDetailInfo = new PrintSalesOrderDetailInfo(reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["DESCR2"].ToString(), reader["UOM_ID"].ToString(), reader["UOM_DESCR"].ToString(), decimal.Parse((reader["ORIGINAL_QTY"].ToString() == "") ? "0" : reader["ORIGINAL_QTY"].ToString()), decimal.Parse((reader["UNIT_PRICE"].ToString() == "") ? "0" : reader["UNIT_PRICE"].ToString()), decimal.Parse((reader["EXTENDED_PRICE"].ToString() == "") ? "0" : reader["EXTENDED_PRICE"].ToString()), reader["REMARK"].ToString(), reader["PACK_ID"].ToString());
                    printSalesOrderDetailInfos.Add(printSalesOrderDetailInfo);
                }
            }
            return printSalesOrderDetailInfos;
        }

        public DataSet GetPrintSalesOrderInfo(string orderID, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintSalesOrder");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintSalesOrder");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetPrintWaveShipmentReport(string waveIDs, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintWaveShipment");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintWaveShipment");
            }
            sql = string.Format(sql, waveIDs).Replace("@_@", tempPrintReportsInfo.WhLoignID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetShippingOrderRptInfoByID(string orderID, List<string> pickDetailIDs, PrintReportsInfo tempPrintReportsInfo)
        {
            string sql = PublicMethod.GetInstance().GetPrintReportSQL(tempPrintReportsInfo.OwnerID + "_PrintShippingOrder");
            if (string.IsNullOrEmpty(sql))
            {
                sql = PublicMethod.GetInstance().GetPrintReportSQL("PrintShippingOrder");
            }
            string sqlPickDetailID = "";
            foreach (string pickDetailID in pickDetailIDs)
            {
                sqlPickDetailID = sqlPickDetailID + string.Format("'{0}',", pickDetailID);
            }
            sqlPickDetailID = sqlPickDetailID.Substring(0, sqlPickDetailID.Length - 1);
            if (!string.IsNullOrEmpty(sqlPickDetailID))
            {
                sql = string.Format(sql, sqlPickDetailID);
            }
            else
            {
                sql = string.Format(sql, "SELECT PICK_DETAIL_ID FROM @_@PICK_DETAIL WHERE ORDER_ID = @ORDER_ID");
            }
            sql = sql.Replace("@_@", tempPrintReportsInfo.WhLoignID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }
    }
}

